﻿using blackhouse.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace blackhouse.WebNemo.Data.Stronger {
    internal class RecordQuery {

        #region Fields

        private string language;
        private SelectColumn[] columns;
        private FromGroup[] tables;
        private List<WhereColumn> where;
        private static Dictionary<Type, DbType> predefinedTypes;
        private Dictionary<string, string> tableAlias = new Dictionary<string, string>();
        private FromGroup firstTable;

        #endregion

        #region Properties

        private static Dictionary<Type, DbType> PredefinedTypes {
            get {
                if (predefinedTypes == null)
                    predefinedTypes = GetPredefinedTypes();
                return predefinedTypes;
            }
        }

        #endregion

        #region Constructors

        public RecordQuery(string language) {
            this.language = language;
        }

        #endregion

        #region Methods

        internal void SetSelectColumns(SelectColumn[] columns) {
            this.columns = columns;
        }

        internal void SetTables(FromGroup[] groups) {
            this.tables = groups;
        }

        internal void SetWhereClauses(WhereColumn[] whereClauses) {
            this.where = whereClauses.ToList();
        }

        internal void AddWhereClause(WhereColumn whereClause) {
            if (this.where == null) this.where = new List<WhereColumn>();
            this.where.Add(whereClause);
        }

        internal IDataReader Execute(MsSqlAdoDataHelper dataHelper) {
            List<string> buildQuerySelect = new List<string>();
            List<string> buildQueryWhere = new List<string>();
            string queryFrom = this.GetFromQueryPart();
            bool timesTableGetter = false;
            foreach (FromGroup table in this.tables) {
                string alias = tableAlias[table.TableName];
                bool isParent = table.TableName.Equals(firstTable.TableName);
                bool isTimeParent = false;
                if (!timesTableGetter && table.GroupType != "View") {
                    isTimeParent = true;
                    timesTableGetter = true;
                }
                buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.Idx, isParent));
                if (table.GroupType == "Table") {
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.Delete, isParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.Identyficator, isParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.CreateTime, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.CreateUser, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.ModTime, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.ModUser, isTimeParent));
                }
                if (table.GroupType == "View") {
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.Identyficator, isParent));
                }
                if (table.GroupType == "ExtendedTable") {
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.CreateTime, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.CreateUser, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.ModTime, isTimeParent));
                    buildQuerySelect.Add(this.GetSelectPart(alias, table.TableName, SystemColumn.ModUser, isTimeParent));
                }
                if (table.GroupType == "Table" || table.GroupType == "FN") {
                    buildQueryWhere.Add(String.Format("({0}.del IS NULL OR {0}.del = 0)", alias));
                }
            }
            buildQuerySelect.AddRange(this.columns.Select(sc => this.GetSelectColumnDeclaration(sc, tableAlias[sc.TableName])));
            foreach (FromGroup limitGroup in this.tables.Where(fg => fg.LimitToIdx > 0)) {
                buildQueryWhere.Add(tableAlias[limitGroup.TableName] + ".idx=" + limitGroup.LimitToIdx);
            }
            StringBuilder query = new StringBuilder("SELECT ");
            query.Append(String.Join(", ", buildQuerySelect.ToArray()) + " FROM " + queryFrom);
            bool addWhere = false;
            if (buildQueryWhere.Count > 0) {
                addWhere = true;
                query.Append(" WHERE ");
                query.Append(String.Join(" AND ", buildQueryWhere.ToArray()));
            }

            dataHelper.Reset();

            foreach (WhereColumn wi in this.where) {
                string alias = tableAlias[wi.TableName];
                string pName = alias.ToUpper() + "P" + wi.ColumnName.ToUpper();
                query.AppendFormat(" {3} {2}.{0} = @{1}", wi.ColumnName, pName, alias, addWhere ? "AND" : "WHERE");
                dataHelper.AddParameter(pName, wi.Where, this.GetDbType(wi.Type));
                addWhere = true;
            }
            dataHelper.Query = query.ToString();
            return dataHelper.Execute();
        }

        internal string GetFromQueryPart() {
            StringBuilder buildQueryFrom = new StringBuilder();
            var firstTableColl = this.tables.Where(fg => fg.GroupType == "Table" || fg.GroupType == "View").Where(fg => String.IsNullOrEmpty(fg.Relation.ParentTableName));
            if (firstTableColl.Count() != 1)
                throw new Exception("FromGroup nie zawiera / zawiera więcej niż jeden deklaracji tabeli bez joina! Z tego nie da się zbudować zapytania");
            this.firstTable = firstTableColl.First();
            tableAlias.Add(firstTable.TableName, Char.ConvertFromUtf32(97 + tableAlias.Count).ToString());
            buildQueryFrom.AppendFormat("{0} {1}", firstTable.TableName, tableAlias[firstTable.TableName]);

            FromGroup joinTable = firstTable;
            while (true) {
                joinTable = tables.FirstOrDefault(fg => fg.Relation.ParentTableName.Equals(joinTable.TableName));
                if (String.IsNullOrEmpty(joinTable.TableName)) break;
                tableAlias.Add(joinTable.TableName, Char.ConvertFromUtf32(97 + tableAlias.Count).ToString());
                if (joinTable.GroupType == "ExtendedTable")
                    buildQueryFrom.Append(" LEFT");
                else
                    buildQueryFrom.Append(" INNER");
                buildQueryFrom.AppendFormat(" JOIN {0} {1} ON {3}.{2} = {1}.{4}", joinTable.TableName, tableAlias[joinTable.TableName], joinTable.Relation.ColumnName, tableAlias[joinTable.Relation.ParentTableName], joinTable.Relation.ParentColumnName);
            }
            return buildQueryFrom.ToString();
        }

        private DbType GetDbType(Type type) {
            if (PredefinedTypes.ContainsKey(type))
                return PredefinedTypes[type];
            else {
                object o = Activator.CreateInstance(type, new object[] { });
                System.Data.SqlClient.SqlParameter sp = new System.Data.SqlClient.SqlParameter("x", o);
                return sp.DbType;
            }
        }

        private string GetSelectColumnDeclaration(SelectColumn column, string alias) {
            if (column.TranslateSupport)
                return String.Format("dbo.bhf_NemoTranslateGet({0}, {3}.idx, '{1}', {3}.{2}) as {2}", column.ColumnId, language, column.ColumnName, alias);
            else
                return alias + '.' + column.ColumnName;
        }

        private string GetSelectPart(string alias, string tableName, SystemColumn column, bool isParentTable) {
            string columnStr = String.Empty;
            switch (column) {
                case SystemColumn.Idx:
                    columnStr = "idx";
                    break;
                case SystemColumn.Delete:
                    columnStr = "del";
                    break;
                case SystemColumn.Identyficator:
                    columnStr = "identyfikator";
                    break;
                case SystemColumn.CreateTime:
                    columnStr = "ctime";
                    break;
                case SystemColumn.CreateUser:
                    columnStr = "cuser";
                    break;
                case SystemColumn.ModTime:
                    columnStr = "mtime";
                    break;
                case SystemColumn.ModUser:
                    columnStr = "muser";
                    break;
            }
            if (isParentTable)
                return String.Format("{0}.{1}", alias, columnStr);
            else
                return String.Format("{0}.{1} as {2}_{1}", alias, columnStr, tableName.ToLower());
        }

        private static Dictionary<Type, DbType> GetPredefinedTypes() {
            Dictionary<Type, DbType> types = new Dictionary<Type, DbType>();
            types.Add(typeof(Boolean), DbType.Byte);
            types.Add(typeof(DateTime), DbType.DateTime);
            types.Add(typeof(Decimal), DbType.Decimal);
            types.Add(typeof(Double), DbType.Double);
            types.Add(typeof(Guid), DbType.Guid);
            types.Add(typeof(Int16), DbType.Int16);
            types.Add(typeof(Int32), DbType.Int32);
            types.Add(typeof(Int64), DbType.Int64);
            types.Add(typeof(Single), DbType.Single);
            types.Add(typeof(String), DbType.String);
            types.Add(typeof(TimeSpan), DbType.Time);
            return types;
        }

        private enum SystemColumn {
            Idx,
            Delete,
            Identyficator,
            CreateTime,
            CreateUser,
            ModTime,
            ModUser
        }

        #endregion

    }
}
